package me.zhengjie.modules.serviceprovider.repository;

import me.zhengjie.modules.serviceprovider.domain.TServiceproviderInfo;
import me.zhengjie.modules.serviceprovider.service.dto.OrderSuggestDTO;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Optional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
* @author xuyukun
* @date 2019-07-20
*/
@SuppressWarnings("rawtypes")
public interface TServiceproviderInfoRepository extends JpaRepository<TServiceproviderInfo, Long>, JpaSpecificationExecutor {
    Optional<TServiceproviderInfo> findByInviteCode(String inviteCode);
    Optional<TServiceproviderInfo> findBySeproCode(String seproCode);

    @Query(value="SELECT provider.sepro_name as seproName, " +
            "count(1) as reciveCount," +
            "count(case when order1.status>=700 then 1 else 0 end) as finishCount," +
            "sum(0) as refuseCount ," +
            "sum(0) as returnCount," +
            "sum(case when order1.service_time<order1.actual_service_time then 1 else 0 end) as timeOutCount," +
            "sum(0) as unArriveCount," +
            "sum(ifnull(order1.service_time,0)) as sumTime," +
            "sum(ifnull(order1.actual_service_time,0)) as acSumTime," +
            "sum(case when order1.satisfaction=0 then 1 else 0 end) as unOkCount," +
            "sum(case when order1.satisfaction=1 then 1 else 0 end) as justOkCount," +
            "sum(case when order1.satisfaction=2 then 1 else 0 end) as okCount," +
            "sum(case when order1.level='初级' then 1 else 0 end) as levelACount," +
            "sum(case when order1.level='中级' then 1 else 0 end) as levelBCount," +
            "sum(case when order1.level='高级' then 1 else 0 end) as levelCCount" +
            " FROM t_serviceprovider_info provider" +
            " INNER JOIN t_work_order order1 ON provider.id = order1.supplier" +
            " where order1.status>=700 and (:refeName is null or provider.sepro_name like CONCAT('%', :refeName, '%'))" +
            "	 and order1.evaluate_time BETWEEN :startTime and :endTime" +
            " GROUP BY order1.supplier",
            nativeQuery = true,
            countQuery = "SELECT count(1)" +
                "FROM t_serviceprovider_info provider" +
                "INNER JOIN t_work_order order1 ON provider.id = order1.supplier " +
                "where order1.status>=700 and (:refeName is null or provider.sepro_name like CONCAT('%', :refeName, '%')) and order1.evaluate_time BETWEEN :startTime and :endTime" +
                "GROUP BY order1.supplier")
    public Page<Object[]> queryProviderEvaluates(@Param("refeName") String refeName,
                                                          @Param("startTime") String startTime,
                                                          @Param("endTime") String endTime, Pageable pageable);


    @Query(value="SELECT provider.sepro_name as seproName, " +
            "count(1) as reciveCount," +
            "count(case when order1.status>=700 then 1 else 0 end) as finishCount," +
            "sum(0) as refuseCount ," +
            "sum(0) as returnCount," +
            "sum(case when order1.service_time<order1.actual_service_time then 1 else 0 end) as timeOutCount," +
            "sum(0) as unArriveCount," +
            "sum(ifnull(order1.service_time,0)) as sumTime," +
            "sum(ifnull(order1.actual_service_time,0)) as acSumTime," +
            "sum(case when order1.satisfaction=0 then 1 else 0 end) as unOkCount," +
            "sum(case when order1.satisfaction=1 then 1 else 0 end) as justOkCount," +
            "sum(case when order1.satisfaction=2 then 1 else 0 end) as okCount," +
            "sum(case when order1.level='初级' then 1 else 0 end) as levelACount," +
            "sum(case when order1.level='中级' then 1 else 0 end) as levelBCount," +
            "sum(case when order1.level='高级' then 1 else 0 end) as levelCCount" +
            " FROM t_serviceprovider_info provider" +
            " INNER JOIN t_work_order order1 ON provider.id = order1.supplier" +
            " where order1.status>=700 and (:refeName is null or provider.sepro_name like CONCAT('%', :refeName, '%'))" +
            "	 and order1.evaluate_time BETWEEN :startTime and :endTime" +
            " GROUP BY order1.supplier",
            nativeQuery = true)
    public List<Object[]> queryAllProviderEvaluates(@Param("refeName") String refeName,
                                                 @Param("startTime") String startTime,
                                                 @Param("endTime") String endTime);


    @Query(value="select distinct info.id,info.sepro_name from t_serviceprovider_person p, t_serviceprovider_info info " +
            " where p.serviceprovider_id=info.id " +
            " and (:major is null or p.major=:major)" +
            " and ( p.level in :level)" +
            " and (:country is null or p.country=:country)" +
            " and (:province is null or p.province=:province)" +
            " and (:city is null or p.city=:city)" +
            " and (:county is null or p.county=:county)",
            nativeQuery = true)


    public List<Object[]> queryServiceproviderInfo(@Param("major") String major,
                                                    @Param("level") ArrayList<String> level,
                                                   @Param("country") String country,
                                                   @Param("province") String province,
                                                   @Param("city") String city,
                                                    @Param("county") String county);
}